package com.sunjq.fetchdata.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

import com.sunjq.fetchdata.object.Notice;

public class FetchDataWaiShiChuDao extends FetchDataBaseDao<Notice> {

	public FetchDataWaiShiChuDao(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	public FetchDataWaiShiChuDao(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	public static final String TAG = "DbHelper";

	public static final String DB_NAME = "notice.db";

	public static final String TABLE_NAME = "NOTICE";
	public static final String COLUMN_NAME_ID = "ID";
	public static final String COLUMN_NAME_VENUE = "VENUE";
	public static final String COLUMN_NAME_DATE = "DATE";
	public static final String COLUMN_NAME_INSERT_DATE = "INSERTDATE";
	public static final String COLUMN_NAME_TITLE = "TITLE";
	public static final String COLUMN_NAME_URL = "DETAILCONTURL";
	public static final String COLUMN_NAME_DESC = "DESC";

	public static final int DB_VERSION = 7;

	private SQLiteDatabase sqliteDB;

	private Cursor cursor;

	private List<Notice> resultList = new ArrayList<Notice>();

	@Override
	public void onCreate(SQLiteDatabase db) {

		String createNoticeSQL = "create table Notice (" + COLUMN_NAME_ID
				+ " integer primary key, " + COLUMN_NAME_VENUE + " text, "
				+ COLUMN_NAME_TITLE + " text Unique, " + COLUMN_NAME_URL
				+ " text Unique, " + COLUMN_NAME_DATE + " text, " + COLUMN_NAME_INSERT_DATE + " long, "
				+ COLUMN_NAME_DESC + " text)";

		db.execSQL(createNoticeSQL);

		Log.d(TAG, "on create sql => " + createNoticeSQL);
	}

	private Notice waiShiChuNotice;

	public Notice getWaiShiChuNotice() {
		return waiShiChuNotice;
	}

	public void setWaiShiChuNotice(Notice waiShiChuNotice) {
		this.waiShiChuNotice = waiShiChuNotice;
	}

	@Override
	public Notice get(String ID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(Notice o) {
		if (checkIfExist(o)) {
			return;
		}
		ContentValues values = new ContentValues();
//		values.put(COLUMN_NAME_ID, "NULL");
		values.put(COLUMN_NAME_VENUE, o.getVenue());
		values.put(COLUMN_NAME_TITLE, o.getTitle());
		values.put(COLUMN_NAME_DATE, o.getDate());
		values.put(COLUMN_NAME_INSERT_DATE, System.currentTimeMillis());
		values.put(COLUMN_NAME_URL, o.getDetailContentUrl());
		values.put(COLUMN_NAME_DESC, o.getDescription());
		sqliteDB = this.getWritableDatabase();
		sqliteDB.insertOrThrow(TABLE_NAME, null, values);

	}

	public boolean checkIfExist(Notice o) {

		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE "
		// + COLUMN_NAME_TITLE + " = '" + o.getTitle() + "' OR "
				+ COLUMN_NAME_URL + " = '" + o.getDetailContentUrl() + "'";
//		System.out.println("existSQL => " + sql);
		sqliteDB = this.getWritableDatabase();
		cursor = sqliteDB.rawQuery(sql, null);
//		System.out.println();
		boolean tmp = cursor.moveToNext();
	
		
		cursor.close();
		return tmp;

	}
	
	public void deleteAll() {
		String sql = "DELETE FROM " + TABLE_NAME;
		sqliteDB = this.getWritableDatabase();
		cursor = sqliteDB.rawQuery(sql, null);
		System.out.println();
		cursor.moveToNext();
		cursor.close();
	}

	@Override
	public void update(Notice o) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(Notice o) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists NOTICE");
		onCreate(db);

	}

	@Override
	public List<Notice> listAll() {
		this.resultList.clear();
		String sql = "SELECT * FROM " + TABLE_NAME + " ORDER BY "
				+ COLUMN_NAME_INSERT_DATE + " DESC ";
		sqliteDB = this.getWritableDatabase();
		cursor = sqliteDB.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			Notice notice = new Notice();

			notice.setDate(cursor.getString(cursor
					.getColumnIndex(COLUMN_NAME_DATE)));
			notice.setTitle(cursor.getString(cursor
					.getColumnIndex(COLUMN_NAME_TITLE)));
			notice.setDescription(cursor.getString(cursor
					.getColumnIndex(COLUMN_NAME_DESC)));
			notice.setDetailContentUrl(cursor.getString(cursor
					.getColumnIndex(COLUMN_NAME_URL)));

			this.resultList.add(notice);
		}
		cursor.close();

		return resultList;
	}

	public int getTotalNumber() {
		int num = 0;
		String sql = "SELECT COUNT(*) FROM " + TABLE_NAME;
		sqliteDB = this.getWritableDatabase();
		cursor = sqliteDB.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			num = cursor.getInt(0);
		}
		System.out.println("Total number from DB => " + num);
		cursor.close();
		return num;
	}

	@Override
	public List<Notice> listByPage(int offset, int limit) {
		this.resultList.clear();
		if (limit == -1) {
			return listAll();
		} else {

			String sql = "SELECT * FROM " + TABLE_NAME + " ORDER BY "
					+ COLUMN_NAME_INSERT_DATE + " DESC " + " LIMIT " + limit
					+ " OFFSET " + offset;

			System.out.println("pagination ---> " + sql);
			sqliteDB = this.getWritableDatabase();
			cursor = sqliteDB.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				Notice notice = new Notice();

				notice.setDate(cursor.getString(cursor
						.getColumnIndex(COLUMN_NAME_DATE)));
				notice.setTitle(cursor.getString(cursor
						.getColumnIndex(COLUMN_NAME_TITLE)));
				notice.setDescription(cursor.getString(cursor
						.getColumnIndex(COLUMN_NAME_DESC)));
				notice.setDetailContentUrl(cursor.getString(cursor
						.getColumnIndex(COLUMN_NAME_URL)));

				this.resultList.add(notice);
			}

			cursor.close();
			return resultList;
		}
	}

}
